package com.think.teaching.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.think.teaching.model.UserExamResult;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;
import org.apache.ibatis.type.JdbcType;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 用户考试记录 Mapper 接口
 * </p>
 *
 * @author think
 * @since 2020-08-12
 */
public interface UserExamResultMapper extends BaseMapper<UserExamResult> {

    //用户搜索考试结果列表，仅返回该用户名下的信息
    @Select("SELECT * FROM user_exam_result WHERE " +
            "student_id=#{userId} AND delflag=0 AND state=1")
    @Result(column = "id",property = "id")
    @Result(column = "id",property = "examResults",one = @One(select =
            "com.think.teaching.mapper.ExamResultMapper.getExamResults",
            fetchType = FetchType.EAGER))
    IPage<UserExamResult> getUserList(IPage<UserExamResult> page,
                                      Integer userId);

    //管理员搜索考试结果列表，按过滤标签 state filter 对批阅状态 check state 进行过滤
    @Select("<script>" +
            "SELECT user_exam_result.*,u.nickname FROM user_exam_result LEFT JOIN think_user.think_user u ON u.id=user_exam_result.student_id WHERE exam_id=#{examId} AND " +
            "user_exam_result.delflag=0 " +
            "<if test='stateFilter==0'>AND check_state=0</if>" +
            "<if test='stateFilter==1'>AND check_state=1</if>" +
            "</script>")
    @Result(column = "id",property = "id")
    @Result(column = "id",property = "examResults",many = @Many(select ="com.think.teaching.mapper" +
            ".ExamResultMapper.getExamResults",fetchType = FetchType.EAGER))
    IPage<Map<String, Object>> getAdminList(IPage<Map<String, Object>> page,
                                       @Param("examId") Integer examId,
                                            @Param("stateFilter")Integer stateFilter);

    //通过 exam id 和 student id 获取该考试的所有题目信息以及该学生在该考试中的作答情况
    @Select("SELECT u.check_state,u.student_id,u.id as result_id,temp.* " +
            "FROM user_exam_result u " +
            "LEFT JOIN(SELECT t.id as exam_id,t.paper_id,t.exam_name,start_time," +
            "end_time,GROUP_CONCAT(CAST(paper_zone.id as char)) as examResults " +
            "FROM exam_test t " +
            "LEFT JOIN paper_zone ON paper_zone.paper_id=t.paper_id WHERE t.id=#{examId}) temp " +
            "ON temp.exam_id =u.exam_id " +
            "WHERE u.student_id=#{studentId} AND u.exam_id=#{examId}")
    @Result(column = "zones=examResults,resultId=result_id",property = "examResults",
            jdbcType = JdbcType.VARCHAR ,many = @Many(select = "com.think.teaching.mapper" +
            ".UserExamResultMapper.getDependentQuestionByZonesAndResultId",fetchType = FetchType.EAGER ))
    List<LinkedHashMap<String,Object>> getDetails( @Param("examId") Integer examId,
                                                   @Param("studentId") Integer studentId);

    /*
     由上层方法 getDetails 通过 @Result 调用
     传入 zones 和 result id 返回全部 question, 按zone type进行分区
     */
    @Select("SELECT #{resultId} as content, id as zone_id,type as zone_type FROM paper_zone WHERE" +
            " FIND_IN_SET(id,#{zones}) ")
    @Result(column = "zone_id",property = "zoneId")
    @Result(column = "zoneId=zone_id,resultId=content",property ="content",many = @Many(select =
            "com.think.teaching.mapper.UserExamResultMapper.getQuestionByZoneIdAndResultId",fetchType = FetchType.EAGER ) )
    List<LinkedHashMap<String,Object>> getDependentQuestionByZonesAndResultId(@Param("zones") String zones,
                                                                              @Param("resultId") Integer resultId);

    /*
     由上层方法 getDependentQuestionByZonesAndResultId 调用
     传入 zone id和 result id 返回该 zone 下全部 question
     */
    @Select("SELECT temp.id,temp.type,temp.answer,analyzing,IFNULL" +
            "(exam_result.answer,'')  as user_answer " +
            "FROM (SELECT * FROM question WHERE question.id " +
            "IN (SELECT paper_question.question_id FROM paper_question WHERE zone_id =#{zoneId}))" +
            " temp " +
            "LEFT JOIN exam_result ON temp.id=exam_result.question_id AND result_id=#{resultId}")
    List<LinkedHashMap<String,Object>> getQuestionByZoneIdAndResultId(@Param("zoneId") Integer zoneId,
                                                                      @Param("resultId") Integer resultId);

       /*  通过 zones 和 result id 返回全部 question, 不按 zone type 进行分区
    @Select("SELECT temp.id,temp.type,temp.answer,analyzing,IFNULL" +
        "(exam_result.answer,'')  as user_answer " +
        "FROM (SELECT * FROM question WHERE question.id " +
              "IN (SELECT paper_question.question_id FROM paper_question WHERE zone_id " +
                  "IN (SELECT id FROM paper_zone WHERE FIND_IN_SET(id,#{zones})))) temp " +
        "LEFT JOIN exam_result ON temp.id=exam_result.question_id AND result_id=#{resultId}")
    List<LinkedHashMap<String,Object>> getIndependentQuestionByZonesAndResultId(String zones,
                                                                      Integer resultId);
    */
    
    @Select("SELECT COUNT(*) FROM user_exam_result WHERE exam_id IN (SELECT id FROM exam_test WHERE exam_test.paper_id IN(SELECT id FROM exam_paper Where course_id=#{courseId})) AND delflag != 1")
    public Integer getUserJoinCourseExamCount(@Param("courseId")Integer courseId);
    


	@Select("<script> "
			+ "SELECT COUNT(*) FROM user_exam_result,exam_test,exam_paper WHERE user_exam_result.exam_id=exam_test.id AND exam_test.paper_id=exam_paper.id AND score&gt;=60 AND score&lt;80"
			+ "<if test='course gt 0'>  AND course_id =#{course}</if>"
			+ " ORDER BY user_exam_result.created_time "
			+ "</script>"
)
	int GetGoodCount(@Param("course")Integer course);
	
	@Select("<script>SELECT COUNT(*) FROM user_exam_result,exam_test,exam_paper WHERE user_exam_result.exam_id=exam_test.id AND exam_test.paper_id=exam_paper.id AND score&gt;=80"
			+ "<if test='course gt 0'>  AND course_id =#{course}</if>"
			+ " ORDER BY user_exam_result.created_time </script>")
	int GeOutstandingCount(@Param("course")Integer course);

	@Select("<script>SELECT user_exam_result.*,exam_paper.title,exam_test.exam_name,student_info.true_name FROM user_exam_result,exam_test,exam_paper,student_info WHERE user_exam_result.exam_id=exam_test.id AND exam_test.paper_id=exam_paper.id AND student_info.user_id=user_exam_result.student_id"
			+ "<if test='course gt 0'>  AND course_id =#{course}</if>"
			+ " ORDER BY user_exam_result.created_time "
			+ "</script>")
	IPage<Map<String, Object>> ListExamResult(IPage<Map<String, Object>> ipage, @Param("course")Integer course);
}
